# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import time
import datetime

from pymysql import NULL

from paper.utils.mysql import Mysql


class PaperPipeline(object):
    def __init__(self):
        # 实例化mysql
        self.mysql = Mysql()

    def process_item(self, item, spider):
        # 乡镇类型名称
        city_name = item['city_name']
        city_type = 2
        if city_name == '阿尔巴斯苏木乡':
            city_type = 2
        elif city_name == '蒙西工业园区':
            city_type = 3
        elif city_name == '蒙西镇':
            city_type = 4
        elif city_name == '木凯淖尔镇':
            city_type = 5
        elif city_name == '棋盘井工业园区':
            city_type = 6
        elif city_name == '棋盘井镇':
            city_type = 7
        elif city_name == '苏米图苏木乡':
            city_type = 8
        elif city_name == '乌兰镇':
            city_type = 9
        if item['status'] == 1:
            result = self.now_day(item, city_type)
        elif item['status'] == 2:
            result = self.seven_days(item, city_type)
        return result

    """
        今日天气
    """

    def now_day(self, item, city_type):
        select_one = 'SELECT id FROM weather_common_dict WHERE value_name= \"%s\"' % (item['wind_dir'])
        result = self.mysql.fetch(select_one, 'one')
        if len(result) > 0:
            # 查询字典表的数据
            item['wind_dir'] = result[0]
        else:
            # 查询不到的 默认为 晴
            item['wind_dir'] = 2
        # 地区格式化的str
        item['region'] = city_type
        # 获取当前的时间
        get_current_time = time.strftime('%Y-%m-%d', time.localtime())
        # 查看指定的地区, 今天数据是否已经存在，存在则更新，否则插入数据
        select_day_there = 'SELECT * FROM weather_now_day WHERE region=\"%s\" and which_day=\"%s\"' % (
            city_type, get_current_time)
        # 是否存在
        select_there_data = self.mysql.fetch(select_day_there, 'one')
        # 创建时间
        created_at = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
        if not select_there_data:
            # 插入数据
            insert_day_data = 'INSERT INTO weather_now_day(temp, humi, wind, wind_dir, which_day, region, created_at) VALUES (\"%s\", \"%s\", \"%s\", %s, \"%s\", \"%s\", \"%s\")' % (
                item['temp'], item['humi'], item['wind'], item['wind_dir'], item['which_day'], item['region'],
                created_at)
            self.mysql.other(insert_day_data)
        elif len(select_there_data) > 0:
            update_sql = 'UPDATE weather_now_day SET temp=\"%s\", humi=\"%s\", wind=\"%s\", wind_dir=%s, which_day=\"%s\", region=\"%s\", created_at=\"%s\" WHERE id=%s' % (
                item['temp'], item['humi'], item['wind'], item['wind_dir'], item['which_day'], item['region'],
                created_at, select_there_data[0])
            self.mysql.other(update_sql)
        return item

    """
        七日天气
    """

    def seven_days(self, item, type):
        # 创建时间
        created_at = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
        # 七天的具体日期
        date_list = [0, 1, 2, 3, 4, 5, 6]
        date_result = []
        item['region'] = type
        for date_item in date_list:
            date_item = self.FormatTomorrow(date_item)
            date_result.append(date_item)
        # 处理数据
        for value in date_list:
            time_date = date_result[value]
            """
                查询数据是否存在
            """
            # 查看指定的地区, 今天数据是否已经存在，存在则更新，否则插入数据
            select_seven_there = 'SELECT * FROM weather_seven_days WHERE region=\"%s\" and which_day=\"%s\"' % (
                type, time_date)
            # 是否存在
            select_there_data = self.mysql.fetch(select_seven_there, 'one')
            print(select_there_data, '是否存在')
            item['weather'][value] = self.FormatDictionary(item['weather'][value])

            # 开始风向 - 结束风判断
            dir_start = self.FormatDictionary(item['dir_start'][value])
            dir_end = self.FormatDictionary(item['dir_end'][value])

            if not select_there_data:
                if dir_start == dir_end:
                    dir_end = NULL
                # 插入数据
                insert_seven_data = 'INSERT INTO weather_seven_days(weather, max_temperature, min_temperature, max_wind, which_day, dir_start, dir_end, created_at, region) VALUES (%s, \"%s\", \"%s\", \"%s\", \"%s\", %s, %s, \"%s\", \"%s\")' % (
                    item['weather'][value], item['max_temperature'][value], item['min_temperature'][value],
                    item['max_wind'][value], time_date,
                    dir_start, dir_end, created_at, item['region'])
                self.mysql.other(insert_seven_data)
            else:
                update_seven_data = 'UPDATE weather_seven_days SET weather=%s, max_temperature=\"%s\", min_temperature=\"%s\", max_wind=\"%s\", which_day=\"%s\", dir_start=%s, dir_end=%s, created_at=\"%s\", region=\"%s\" WHERE id=%s' % (
                    item['weather'][value], item['max_temperature'][value], item['min_temperature'][value],
                    item['max_wind'][value], time_date,
                    dir_start, dir_end, created_at, item['region'], select_there_data[0])
                self.mysql.other(update_seven_data)
                print('数据修改成功~')
        return item

    """
        七天后的日期
    """

    def FormatSevenDay(self):
        today = datetime.datetime.today()
        tomorrowemp = today + datetime.timedelta(hours=24 * 6)
        # 或者tomorrowemp = today + datetime.timedelta(days = 7)
        tomorrow = tomorrowemp.replace(hour=0, minute=0, second=0, microsecond=0)
        return tomorrow

    """
        日期 date是0 - 今天
    """

    def FormatTomorrow(self, date):
        today = datetime.datetime.today()
        tomorrowemp = today + datetime.timedelta(hours=24 * date)
        # 或者tomorrowemp = today + datetime.timedelta(days = 1)
        tomorrow = tomorrowemp.replace(hour=0, minute=0, second=0, microsecond=0)
        return tomorrow

    """
        查询字典 - 风向
    """

    def FormatDictionary(self, value):
        r_result = ''
        select_one = 'SELECT id FROM weather_common_dict WHERE value_name= \"%s\"' % (value)
        result = self.mysql.fetch(select_one, 'one')
        if result and len(result) > 0:
            # 查询字典表的数据
            r_result = result[0]
        else:
            # 查询不到的 默认为 晴
            r_result = 2
        return r_result
